package.cpath = "luaclib/?.so;3rd/lua-cjson/?.so;"
local skynet = require "skynet"
local socket = require "socket"
require "skynet.manager" -- import skynet.register
require "config_redis"
local mysql = require "mysql"
local redis = require "redis"
local json = require "cjson"
json.encode_sparse_array(true)


local mysql_db
local function start_mysql(args)
    local function on_connect(db)
        db:query("set charset " .. args.charset);
    end

    mysql_db = mysql.connect {
        host = args.host,
        port = args.port,
        database = args.database,
        user = args.user,
        password = args.password,
        max_packet_size = 1024 * 1024,
        on_connect = on_connect,
    }
end

local redis_db
local function start_redis(args)
    redis_db = redis.connect(args)
end

CMD = {}

--------------------------------- begin mysql function---------------------------------
function goto_mysql_db_query(sql)
    return mysql_db:query(sql)
end

function mysql_db_query(sql)
    return mysql_db:query(sql)
end

function mysql_db_query_re(sql)
    mysql_db:disconnect()
    start_mysql(mysql_args)
    return goto_mysql_db_query(sql)
end

function CMD.mysql_get_one(sql)
    local ok = false
    sql = sql .. ' limit 0,1'
    local res = mysql_db_query(sql)
    if #res == 0 then
        return nil
    end
    ok = true
    return res[1]
end

function CMD.mysql_get_all(sql)
    local ok = false
    local res = mysql_db_query(sql)
    if #res == 0 then
        return ok
    end
    return res
end

function CMD.mysql_insert(sql)
    local res = mysql_db_query(sql)
    local ok = false
    if res and res.errno then
        return ok
    end
    ok = res.insert_id
    return ok
end

function CMD.mysql_update(sql)
    mysql_db_query(sql)
end

function CMD.mysql_del(sql)
    mysql_db_query(sql)
end

--------------------------------- begin redis function---------------------------------
-- function CMD.redis_get(key)   
-- return redis_db:get( key )
-- end
-- del  key
function CMD.redis_del(key)
    return redis_db:del(key)
end

--hget  key 
function CMD.redis_hgetall(key)
    local data = redis_db:hgetall(key)
    local rs = {}
    if data ~= nil then
        for k, v in pairs(data) do
            if k % 2 == 1 then
                rs[v] = data[k + 1]
            end
        end
    end
    return rs
end

function CMD.redis_hget(key, feid)
    local data = redis_db:hget(key, feid)
    skynet.error("-- mg_save -- CMD.redis_hget -------> key, feid, data:", key, feid, data)
    return data
end

function CMD.redis_hset(key, feid, v)
    skynet.error("-- mg_save -- CMD.redis_hset -------> key, feid, v:", key, feid, v)
    return redis_db:hset(key, feid, v)
end

function CMD.redis_hincrby(key, feid, v)
    return redis_db:hincrby(key, feid, v)
end

--del  key 
function CMD.redis_hdel(key, feid)
    return redis_db:hdel(key, feid)
end

--rpush  在名称为key的list尾添加一个值为value的元素 
function CMD.redis_rpush(key, value)
    return redis_db:rpush(key, value)
end

--lpush  在名称为key的list头添加一个值为value的 元素
function CMD.redis_lpush(key, value)
    return redis_db:lpush(key, value)
end

--返回名称为key的list的长度
function CMD.redis_llen(key)
    return redis_db:lpush(key)
end

--返回名称为key的list中start至end之间的元素（下标从0开始
function CMD.redis_lrange(key, start, endnum)
    return redis_db:lrange(key, start, endnum)
end

--返回名称为key的list中start至end之间的元素（下标从0开始
function CMD.redis_ltrim(key, start, endnum)
    return redis_db:ltrim(key, start, endnum)
end

--根据参数 count 的值，移除列表中与参数 value 相等的元素。
function CMD.redis_lrem(key, count, value)
    return redis_db:lrem(key, count, value)
end

--NX - 仅在键不存在时设置键
function CMD.redis_setnx(key, value, expires)
    return redis_db:set(key, value, 'EX', expires, 'NX')
end

--获取value
function CMD.redis_get(key)
    return redis_db:get(key)
end

--key是否存在
function CMD.redis_exists(key)
    return redis_db:exists(key)
end

--------------------------------- end redis function---------------------------------

-- 根据账号查找uid
function CMD.get_user_info(uid)
    local res = {}
    local query_sql = string.format("SELECT name,head,phone_num,score,no_say,ai,tourist,inte_rate,status FROM `user` WHERE uid = %d", uid);
    print('-----------get_user_info-----query_sql------------', query_sql)
    local res = mysql_db_query(query_sql);
    print('-----------get_user_info-----#res------------', #res)
    if #res == 0 then
        return 0
    else
        return res[1]
    end
end

--更新token查用户
function CMD.login_get_account(account)
    local query_sql
    local res = {}
    query_sql = string.format("select * from user where token = '%s' order by uid", account);
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return 0, 0
    else
        return res[1]['uid'], res[1]['status']
    end
end

--获取用户信息
function CMD.getUserInfo(uid)
    local query_sql
    query_sql = string.format("select * from user where uid = '%d'", uid);
    local res = mysql_db_query(query_sql);
    return res
end

--获取有余分的用户
function CMD.getUserHasScore()
    local query_sql
    query_sql = string.format("select * from user where score > 0 and deleted=0");
    local res = mysql_db_query(query_sql);
    return res
end


--更新用户数据
function CMD.update_user(uid, type, num, sql)
    local ok = 0
    local query_sql = string.format("update user set %s =%s + %d where uid = %d", type, type, num, uid)
    if sql ~= nil then
        query_sql = sql
    end
    local res = mysql_db_query(query_sql)
    if res then
        ok = 1
    else
        ok = 0
    end
    -- print('======CMD.update_user==========',uid, type,num,query_sql,ok)
    return ok
end

--获取所有用户的分数,不包含机器人和游客
function CMD.get_user_score_all()
    local query_sql = string.format("select sum(score) as  allscore from user where ai=0 and tourist=0")
    local res = mysql_db_query(query_sql)
    if res ~= nil and res[1] ~= nil and res[1]['allscore'] ~= nil then
        return res[1]['allscore']
    else
        return 0
    end
end

--获取游客分数
function CMD.get_user_score_all_tourist()
    local query_sql = string.format("select sum(score) as  allscore from user where tourist=1 and ai=0")
    local res = mysql_db_query(query_sql)
    if res ~= nil and res[1] ~= nil and res[1]['allscore'] ~= nil then
        return res[1]['allscore']
    else
        return 0
    end
end

--获取机器人分数
function CMD.get_user_score_all_ai()
    local query_sql = string.format("select sum(score) as  allscore from user where ai=1")
    local res = mysql_db_query(query_sql)
    if res ~= nil and res[1] ~= nil and res[1]['allscore'] ~= nil then
        return res[1]['allscore']
    else
        return 0
    end
end

--执行sql语句
function CMD.update_sql(query_sql)
    local res = mysql_db_query(query_sql)
    return res
end

--执行update sql语句
function CMD.do_update_sql(query_sql)
    local res = mysql_db_query(query_sql)
    local temp = split(res['message'], ':')
    local temp1 = split(temp[3], ' ')
    local temp2 = tonumber(temp1[1])
    return temp2
end

--插入聊天记录
function CMD.insert_chat_log(data)
    local query_sql = string.format("insert into `chat_log` (uid,msg,time,card_game_id,room_id,boots_number,ju,type,score)values(%d,'%s','%s',%d,%d,%d,%d,%d,%f)", data.uid, data.msg, data.time, data.card_game_id, data.room_id, data.boots_number, data.ju, data.type, data.score)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--根据时间范围，查找上下分总额
function CMD.get_fen_all_by_type(data)
    local query_sql = string.format("select sum(score_change) as money_change from `user_score_log` where time>='%s' and time<='%s' and type in(%s) and user_ai=0 and and tourist=0", data.begin_time, data.end_time, data.type)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return nil
    end
    return res
end

--保存自营报表数据
function CMD.insert_zy_tongji(data)
    local query_sql = string.format("insert into `zy_tongji` (tmbj,tmcm,wsyk,sbyk,dcyk,dlzy,zxxm,sbxm,khyk,score_all,score_old_all,ai_score_all,tourist_score_all,upfen_all,dowfen_all,deleted,mktime) values(%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%f,%d,'%s')", data.tmbj, data.tmcm, data.wsyk, data.sbyk, data.dcyk, data.dlzy, data.zxxm, data.sbxm, data.khyk, data.score_all, 0, 0, 0, data.upfen_all, data.dowfen_all, 0, os.date("%Y-%m-%d %H:%M:%S"))
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--插入各个总分记录
function CMD.insert_score_all(score_all, score_old_all, ai_score_all, tourist_score_all, start, card_game_id)
    local query_sql = string.format("insert into `score_all` (score_all,score_old_all,ai_score_all,tourist_score_all,mktime,start,card_game_id) values (%f,%f,%f,%f,'%s',%d,%d)", score_all, score_old_all, ai_score_all, tourist_score_all, os.date("%Y-%m-%d %H:%M:%S"), start, card_game_id)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--查询各个总分
function CMD.select_score_all()
    local query_sql = string.format("select * from `score_all` order by id desc limit 1")
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return nil
    end
    return res
end


--插入下注记录
function CMD.inser_bets_log(data)
    local query_sql = string.format("insert into `bets_log` (uid,card_game_id,time,text, type,odds,score,win,state,rate,user_ai,tourist) values( %d, %d,'%s','%s', %d,%f, %f,0,0,%f,%d,%d)", data.uid, data.card_game_id, data.time, data.text, data.type, data.odds, data.score, data.rate, data.user_ai, data.tourist)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--获取下注记录
function CMD.get_bets_log(where)
    local query_sql
    local res = {}
    query_sql = "select * from bets_log where " .. where;
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return {}
    else
        return res
    end
end

--合并下注记录
function CMD.bets_log_merge(data)
    local query_sql = string.format("INSERT INTO `bets_merge`(uid,nickname,zhuang,xian,he,zhuang_dui,xian_dui,score_before,score_after,win,game_zhuang,game_zhuang_dui,game_xian_dui,state,mktime,card_game_id,room_id,boots_number,ju)VALUES(%d,'%s',%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%.2f,%d,%d,%d,%d,%d,%d,%d,%d,%d)"
        ,data.uid,data.nickname,data.zhuang,data.xian,data.he,data.zhuang_dui,data.xian_dui,data.score_before,data.score_after,data.win,data.game_zhuang,data.game_zhuang_dui,data.game_xian_dui,data.state,data.mktime,data.card_game_id,data.room_id,data.boots_number,data.ju)
    print('-------------query_sql--bets_data-----------',query_sql)
    local res = mysql_db_query(query_sql);
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--插入积分变化记录
function CMD.inser_user_score_log(data)
    local query_sql = string.format("insert into `user_score_log` (uid,score,score_change,type,time,card_game_id,score_after,user_ai,tourist,ukey,orderid) values( %d, %f, %f, %d,'%s',%d,%f,%d,%d,'%s',%d)", data.uid, data.score, data.score_change, data.type, data.time, data.card_game_id, data.score_after, data.user_ai, data.tourist, data.ukey, data.orderid)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--查询用户最后一条积分记录
function CMD.select_user_score_log_last(uid)
    local query_sql = string.format("select * from `user_score_log` where uid=%d order by id desc limit 1", uid)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res
end

function CMD.inser_user_money_log(data)
    local query_sql = string.format("insert into `user_money_log` (uid,money,money_change, type,time,money_type,text,yh_name,yh_hao,user_ai,tourist,mktime) values( %d, %f, %f, %d,'%s', %d,'%s','%s','%s',%d,%d,'%s' )", data.uid, data.money, data.money_change, data.type, data.time, data.money_type, data.text, data.yh_name, data.yh_hao, data.user_ai, data.tourist, data.mktime)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--余分错误日志
function CMD.inser_score_error(data)
    local query_sql = string.format("insert into `score_error` (uid,score,score_log,mktime) values( %d, %f, %f,'%s')", data.uid, data.score, data.score_log, data.mktime)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

function CMD.upfen(data)
    local query_sql = string.format("insert into `user_money_log` (uid,money,money_change, type,time,money_type,state,user_ai,tourist,mktime) values( %d, %f, %f, %d,'%s', %d ,%d,%d ,%d,'%s')", data.uid, data.money, data.money_change, data.type, data.time, data.money_type, data.state, data.user_ai, data.tourist,data.mktime)
    local res = mysql_db_query(query_sql)
    if res and res.errno then
        return 0
    end
    return res.insert_id
end

--查询上分记录
function CMD.select_upfen_log(uid)
    local query_sql = string.format("select unix_timestamp(time) as time,uid,money_change,type  from `user_money_log` where uid=%d and state=0 and type=11 and money_change>0 order by id desc limit 1 ", uid)
    local res = mysql_db_query(query_sql)
    return res
end

function CMD.get_use_ask(id)
    local query_sql
    local res = {}
    query_sql = string.format("select * from use_ask where id = %d", id);
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return {}
    else
        return res[1]
    end
end

--绑定手机号
function CMD.inser_use_ask(data)
    local query_sql = string.format("insert into `use_ask` (uid, type,odds,time,state) values( %d, %d, %d, '%s',0 )", data.uid, data.type, data.odds, data.time)
    local res = mysql_db_query(query_sql)
end

--绑定手机号
function CMD.update_use_ask(id, state)
    local ok = 0
    local query_sql = string.format("update use_ask set state = %d where id = %d", state, id)
    local res = mysql_db_query(query_sql)
    if res then
        ok = 1
    else
        ok = 0
    end
    return ok
end

--获取系统设置
function CMD.get_system()
    local query_sql
    local res = {}
    query_sql = string.format("SELECT * FROM `system` order by id desc limit 0,1");
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return {}
    else
        return res[1]
    end
end

--获取前端域名
function CMD.get_web_domain()
    local query_sql
    local res = {}
    query_sql = string.format("select domain ,type from domain where status=0 and type=7");
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return {}
    else
        return res[1]
    end
end

--获取后台域名
function CMD.get_admin_domain()
    local query_sql
    local res = {}
    query_sql = string.format("select domain ,type from domain where status=0 and type=6");
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return {}
    else
        return res[1]
    end
end

--获取最后一局牌局信息
function CMD.get_card_game()
    local query_sql
    local res = {}
    query_sql = string.format("select * from card_game order by id desc limit 1");
    local res = mysql_db_query(query_sql);
    if #res == 0 then
        return {}
    else
        return res[1]
    end
end

--插入牌局信息
function CMD.inser_card_game(data, sql)
    local query_sql = nil
    if sql ~= nil then
        query_sql = sql
    else
        query_sql = string.format("insert into `card_game` (room_id, boots_number,ju,time,ukey) values( %d, %d, %d, '%s','%s' )", data.room_id, data.boots_number, data.ju, data.time,data.ukey)
    end
    print('inser_card_game==================', query_sql)
    local res = mysql_db_query(query_sql)
    if res.insert_id ~= nil then
        return res.insert_id
    else
        return 0
    end
end

--当局报表统计
--庄注，闲注，和注，庄对，闲对，三宝
function CMD.get_dj_tongji(card_game_id, type)
    local query_sql
    query_sql = string.format("select sum('odds') as odds from bets_log where card_game_id = %d and type = %d and state = 0", card_game_id, type);
    local res = mysql_db_query(query_sql)
    return res
end

--自营报表统计
function CMD.get_zy_tongji()
    local query_sql
    query_sql = string.format("select sum(tmyk) as tmyk,sum(wsyk) as wsyk,sum(sbyk) as sbyk ,sum(dcyk) as dcyk,sum(dlzy) as dlzy,sum(zxxm) as zxxm,sum(sbxm) as sbxm  from card_game where state=1 and guiling=0");
    local res = mysql_db_query(query_sql)
    return res
end

--查询上一局信息
function CMD.select_last_ju_info(card_game_id)
    local query_sql
    query_sql = string.format("select * from card_game where id < %d and state=1 and guiling=0 order by id desc limit 1", card_game_id)
    local res = mysql_db_query(query_sql)
    return res
end

--查询当前一局信息
function CMD.select_cur_ju_info(card_game_id)
    local query_sql
    query_sql = string.format("select * from card_game where id = %d ", card_game_id)
    local res = mysql_db_query(query_sql)
    return res
end

function CMD.get_user_max_uid()
    local query_sql
    local res = {}
    query_sql = string.format("select max(uid) as uid from user ");
    local res = mysql_db_query(query_sql);
    return res[1]['uid']
end



-- 获取用户数据
function CMD.get_user(uid, sql)
    if sql ~= nil then
        query_sql = sql
        local res = mysql_db_query(query_sql)
        return res
    else
        local ok = false
        local query_sql = string.format("select * from user where uid = %d", uid)
        local res = mysql_db_query(query_sql)

        if #res == 0 then
            return nil
        end
        return res[1]
    end
end

-- 保存数据要防止重入问题，待解决
function CMD.unset_user(uid, key, index)
    local ok = false
    local save = {}
    local newKey = key .. "." .. index -- 使用内嵌访问的方式修改数组
    save[newKey] = 1
    --mongo_db[db_name].testdb:findAndModify({query = {uid = uid}, update = {["$unset"] = save}, upsert = true })
    ok = true
    return ok
end

function get_uid()
    math.randomseed(tonumber(tostring(os.time()):reverse():sub(1, 6)))
    local uid = math.random(100000, 999999);
    local query_sql = string.format("select * from user where uid = %d", uid)
    local res = mysql_db_query(query_sql)

    if #res == 0 then
        return uid
    else
        return get_uid()
    end
end


function CMD.set_user(uid, key, index, save)
    local ok = false
    if index then
        local newKey = key .. "." .. index -- 使用内嵌访问的方式修改数组
        save[newKey] = save[key][index]
        save[key] = nil
    end

    local sql = "update user set "
    local set
    for k, v in pairs(save) do
        if set == nil then
            set = string.format("%s='%s'", k, v)
        else
            set = string.format("%s='%s',", k, v) .. set
        end
    end
    sql = sql .. set .. " where uid = " .. uid
    local ret = mysql_db_query(sql)

    if ret then
        ok = true
    else
        ok = false
    end
    return ok
end


skynet.start(function()
    start_mysql(mysql_args)
    start_redis(redis_conf)
    skynet.dispatch("lua", function(_, _, command, ...)
        local f = CMD[command]
        skynet.error("-- mg_save ------->  do CMD." .. command)
        skynet.ret(skynet.pack(f(...)))
    end)
    skynet.register "MG_SAVE"
end)